﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using MediaBrowserSL.BOL;
using MediaBrowserSL.DAL.Contexts;
using MediaBrowserSL.IDAL;

namespace MediaBrowserSL.DAL
{
    public class SqlMediaRepository : IMediaRepository
    {
        /// <summary>
        /// Return list of Media
        /// </summary>
        /// <param name="idUser"></param>
        /// <param name="idCategory"></param>
        /// <returns></returns>
        public IEnumerable<Media> GetListMediasByCategory(int idUser, int idCategory)
        {
            List<Media> medias = new List<Media>();
            String prock = "select Media.Id,Media.Name,Media.Description,Media.UrlContent,Media.UrlImage,Media.CreationDate,UserMedia.Login  from Media, UserMedia ";
            prock += "where Media.Category_Id = @idCategory and Media.User_Id = @idUser and Media.User_Id = UserMedia.Id ";
            prock += "and  Media.Media_Id IS null";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idCategory", idCategory, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idUser", idUser, DbType.Int32));

                        using (DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Media m = new Media()
                                {
                                    Id = (reader["Id"].ToString().Length > 0) ? reader.GetInt32(reader.GetOrdinal("Id")) : 0,
                                    Name = (reader["Name"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Name")) : String.Empty,
                                    Description = (reader["Description"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Description")) : String.Empty,
                                    UrlContent = (reader["UrlContent"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("UrlContent")) : String.Empty,
                                    UrlImage = (reader["UrlImage"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("UrlImage")) : String.Empty,
                                    CreationDate = (reader["CreationDate"].ToString().Length > 0) ? reader.GetDateTime(reader.GetOrdinal("CreationDate")) : DateTime.Now

                                };

                                if (reader["Login"].ToString().Length > 0)
                                {
                                    m.User = new User() { Login = reader["Login"].ToString() };
                                }

                                m.Category = new Category() { Id = idCategory };
                                medias.Add(m);
                            }
                        }
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return medias;
        }

        /// <summary>
        /// Return list of Media Item
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Media> GetListMediaItems(int idMedia, int idUser, int idCategory)
        {
            List<Media> medias = new List<Media>();
            String prock = "select Media.Id,Media.Name,Media.Description,Media.UrlContent,Media.UrlImage,Media.CreationDate,UserMedia.Login  from Media, UserMedia ";
            prock += "where Media.Media_Id = @idMedia and Media.User_Id = @idUser  and Media.User_Id = UserMedia.Id";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idMedia", idMedia, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idUser", idUser, DbType.Int32));


                        using (DbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Media m = new Media()
                                {
                                    Id = (reader["Id"].ToString().Length > 0) ? reader.GetInt32(reader.GetOrdinal("Id")) : 0,
                                    Name = (reader["Name"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Name")) : String.Empty,
                                    Description = (reader["Description"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("Description")) : String.Empty,
                                    UrlContent = (reader["UrlContent"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("UrlContent")) : String.Empty,
                                    UrlImage = (reader["UrlImage"].ToString().Length > 0) ? reader.GetString(reader.GetOrdinal("UrlImage")) : String.Empty,
                                    CreationDate = (reader["CreationDate"].ToString().Length > 0) ? reader.GetDateTime(reader.GetOrdinal("CreationDate")) : DateTime.Now

                                };

                                if (reader["Login"].ToString().Length > 0)
                                {
                                    m.User = new User() { Login = reader["Login"].ToString() };
                                }

                                m.Category = new Category() { Id = idCategory };
                                medias.Add(m);
                            }
                        }
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return medias;
        }

        /// <summary>
        /// Create New Media
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public Media CreateMedia(Media m, User u)
        {
            String prock = "INSERT INTO Media VALUES(@name,@description,@urlContent,@urlImage,@size,@category,@userId,null,@creationDate)";
            prock += " SELECT @@Identity ";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@name", m.Name, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@description", m.Description, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@urlContent", m.UrlContent, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@urlImage", m.UrlImage, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@size", m.Size, DbType.Double));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@category", m.Category.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@userId", u.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@creationDate", m.CreationDate, DbType.DateTime));
                        m.Id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return m;
        }

        /// <summary>
        /// Update  Media
        /// </summary>
        /// <param name="m"></param>
        public void UpdateMedia(Media m)
        {
            String prock = "UPDATE  Media SET ";
            prock += "Name = @name , Description = @description ";
            prock += "where Id = " + m.Id;

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@name", m.Name, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@description", m.Description, DbType.String));

                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
        }

        /// <summary>
        /// Delete  Media
        /// </summary>
        /// <param name="m"></param>
        public void DeleteMedia(Media m)
        {
            String prock = "DELETE FROM Media WHERE Id = @id";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@id", m.Id, DbType.Int32));
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
        }

        /// <summary>
        /// Create Media Item
        /// </summary>
        /// <param name="mediaItem"></param>
        /// <param name="mediaParent"></param>
        /// <param name="u"></param>
        /// <returns></returns>
        public Media CreateMediaItem(Media mediaItem, Media mediaParent, User u)
        {
            String prock = "INSERT INTO Media VALUES(@name,@description,@urlContent,@urlImage,@size,@category,@userId,@mediaId,@creationDate)";
            prock += " SELECT @@Identity ";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@name", mediaItem.Name, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@description", mediaItem.Description, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@urlContent", mediaItem.UrlContent, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@urlImage", mediaItem.UrlImage, DbType.String));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@size", mediaItem.Size, DbType.Double));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@category", mediaItem.Category.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@userId", u.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@mediaId", mediaParent.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@creationDate", mediaItem.CreationDate, DbType.DateTime));
                        mediaItem.Id = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }
            return mediaItem;
        }

        /// <summary>
        /// Get Sum Size By Category
        /// </summary>
        /// <param name="u"></param>
        /// <param name="idCategory"></param>
        /// <returns></returns>
        public double GetSizeByCategory(User u, int idCategory)
        {
            double size = 0;
            String prock = "SELECT SUM(Size) AS Sum FROM  Media ";
            prock += "where Media.User_Id = @idUser and Media.Category_Id = @idCategory  ";

            try
            {
                using (DbConnection db = SqlDataContext.DataContext)
                {
                    using (DbCommand cmd = db.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = prock;
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idUser", u.Id, DbType.Int32));
                        cmd.Parameters.Add(GenericParameterProc.CreateParameter(cmd, "@idCategory", idCategory, DbType.Int32));

                        object res = cmd.ExecuteScalar();
                        if (res != null)
                        {
                            if (!String.IsNullOrEmpty(res.ToString()))
                            {
                                size = (double)res;
                            }
                        }
                    }
                }
            }
            catch (DbException sqlEx)
            {
                throw new SqlException(prock, "", sqlEx);
            }

            return size;
        }
    }
}
